-- @owner: songjing20
-- @date: 2024-7-15
-- @testpoint: 对普通表的触发器进行操作

--step1:建普通表并插入数据，创建触发器;expect:成功
CREATE TABLE t_ustore_alter_table_0014(
 ADD_COL_1 CHAR(1),
 ADD_COL_2 CHAR(10),
 ADD_COL_3 CHAR(102400),
 ADD_COL_4 VARCHAR(1),
 ADD_COL_5 VARCHAR(10),
 ADD_COL_6 VARCHAR(1024),
 ADD_COL_7 INTEGER,
 ADD_COL_8 BIGINT,
 ADD_COL_9 SMALLINT,
 ADD_COL_10 FLOAT,
 ADD_COL_11 numeric(10,5),
 ADD_COL_12 double precision,
 ADD_COL_13 DATE,
 ADD_COL_14 TIMESTAMP WITHOUT TIME ZONE,
 ADD_COL_15 TIMESTAMP WITH TIME ZONE
 CONSTRAINT t_ustore_alter_table_0014_KEY CHECK(ADD_COL_8 > 0)
 )WITH (STORAGE_TYPE=USTORE);
create unique index i_ustore_alter_table_0014 on t_ustore_alter_table_0014 (ADD_COL_1,ADD_COL_7,ADD_COL_10,ADD_COL_14);
insert into t_ustore_alter_table_0014 values('A','ABC','ABCDEFG','a','abc','abcdefg',111,111111,11,1.1,1.11,1.111,'2000-01-01','2000-01-01 01:01:01','2000-01-01 01:01:01+01');
INSERT INTO t_ustore_alter_table_0014 values('B','BCD','BCDEFGH','b','bcd','bcdefgh',222,222222,22,2.2,2.22,2.222,'2000-02-02','2000-02-02 02:02:02','2000-02-02 02:02:02+02');
INSERT INTO t_ustore_alter_table_0014 values('C','CDE','CDEFGHI','c','cde','cdefghi',333,333333,33,3.3,3.33,3.333,'2000-03-03','2000-03-03 03:03:03','2000-03-03 03:03:03+03');
INSERT INTO t_ustore_alter_table_0014 values('D','DEF','DEFGHIJ','d','def','defghij',444,444444,44,4.4,4.44,4.444,'2000-04-04','2000-04-04 04:04:04','2000-04-04 04:04:04+04');
CREATE OR REPLACE FUNCTION TRI_DELETE_FUNC() RETURNS TRIGGER AS
           $$
           DECLARE
           BEGIN
                   DELETE FROM t_ustore_alter_table_0014 WHERE ADD_COL_7=OLD.ADD_COL_7;
                   RETURN OLD;
           END
           $$ LANGUAGE PLPGSQL;/
--创建触发器
CREATE TRIGGER delete_trigger
           BEFORE DELETE ON t_ustore_alter_table_0014
           FOR EACH ROW
           EXECUTE PROCEDURE tri_delete_func();/

--step2:普通表插入数据后对触发器进行操作;expect:成功
ALTER TABLE IF EXISTS t_ustore_alter_table_0014 DISABLE TRIGGER delete_trigger;
ALTER TABLE IF EXISTS t_ustore_alter_table_0014 DISABLE TRIGGER USER;
ALTER TABLE IF EXISTS t_ustore_alter_table_0014 ENABLE TRIGGER ALL;
ALTER TABLE IF EXISTS t_ustore_alter_table_0014 ENABLE TRIGGER USER;
ALTER TABLE IF EXISTS t_ustore_alter_table_0014 ENABLE TRIGGER delete_trigger;
ALTER TABLE IF EXISTS t_ustore_alter_table_0014 ENABLE REPLICA TRIGGER delete_trigger;
ALTER TABLE IF EXISTS t_ustore_alter_table_0014 ENABLE ALWAYS TRIGGER delete_trigger;
ALTER TABLE IF EXISTS t_ustore_alter_table_0014 DISABLE ROW LEVEL SECURITY;
ALTER TABLE IF EXISTS t_ustore_alter_table_0014 ENABLE ROW LEVEL SECURITY;
ALTER TABLE IF EXISTS t_ustore_alter_table_0014 FORCE ROW LEVEL SECURITY;
ALTER TABLE IF EXISTS t_ustore_alter_table_0014 NO FORCE ROW LEVEL SECURITY;

--step3:清理环境;expect:成功
drop trigger delete_trigger on t_ustore_alter_table_0014;
drop function TRI_DELETE_FUNC();
drop index if exists i_ustore_alter_table_0014;
drop table t_ustore_alter_table_0014;
